/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package sys.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import sys.bean.CaixaBean;
import sys.bean.ClienteBean;
import sys.bean.ContasReceberBean;
import sys.bean.PedidoBean;
import sys.bean.UsuarioBean;
import sys.utils.ConexaoADS;
import static sys.utils.Utils.dataCorreta;

/**
 *
 * @author Administrador
 */
public class ContasReceberDao {

    public static double pgtosEmVale(String codCliente) throws SQLException, IOException {
        double total = 0;

        Connection con = ConexaoADS.getConection();

        String sql = "SELECT SUM(C12VRVALE) AS VALE FROM SPAC12 \n"
                + "WHERE C12CODCLI = '" + codCliente + "'";
        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            total += rs.getDouble("VALE");
        }

        stmt.close();
        con.close();

        return total;
    }

    /*
     retornando total de contas pagas/em aberto por cliente
     se o parametro for A am aberto - P Pago
     */
    public static double totalCliente(String codCliente, String parametro) throws SQLException, IOException {
        double total = 0;
        Connection con = ConexaoADS.getConection();

        String campo;
        if (parametro.equals("A")) {
            campo = "C12VRPAR-C12VRPGTO";
        } else {
            campo = "C12VRPGTO";
        }
        String sql = "SELECT SUM(" + campo + ") AS TOTAL FROM SPAC12 \n"
                + "WHERE C12CODCLI = '" + codCliente + "' \n"
                + "AND C12FLGREC = '" + parametro + "'";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            total += rs.getDouble("TOTAL");
        }

        stmt.close();
        con.close();

        return total;
    }
    /*
     se parametro for:
     A' AND C12DTVEN > 'DATA' RETORNA TOTAL A VENCER
    
     se for:
     P' AND C12DTVEN < 'DATA' RETORNA TOTAL VENCIDO
     */

    public static double totalCliente(String codCliente, String data, String parametro) throws SQLException, IOException {
        double total = 0;
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT SUM(C12VRPAR-C12VRPGTO) AS TOTAL FROM SPAC12 \n"
                + "WHERE C12CODCLI = '" + codCliente + "' \n"
                + "AND C12FLGREC = '" + parametro + "'" + data + "'";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            total += rs.getDouble("TOTAL");
        }

        stmt.close();
        con.close();

        return total;
    }

    public static List<ContasReceberBean> listaContasCliente(String idCliente, String flag) throws SQLException, IOException {
        List<ContasReceberBean> listaContas = new ArrayList<>();
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT C12NRDOC, C12DTCOMP, C12DTVEN, C12DTREC,C12FLGREC, C12VRPAR, C12VRPGTO+(C12JUROS-C12DESCTO) AS VALORPGTO, C12NRECTO \n"
                + "FROM SPAC12 \n"
                + "WHERE \n"
                + "C12FLGREC IN ('" + flag + "') \n"
                + "AND C12CODCLI = '" + idCliente + "' \n"
                + "ORDER BY 5,3";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {

            ContasReceberBean cReceber = new ContasReceberBean();

            cReceber.setnDocumento(rs.getString("C12NRDOC"));
            cReceber.setDataCompra(rs.getString("C12DTCOMP"));
            cReceber.setDataVencimento(rs.getString("C12DTVEN"));
            cReceber.setDataPagamento(rs.getString("C12DTREC"));

            if (rs.getString("C12FLGREC").equals("A")) {
                cReceber.setFlagSituacao("PENDENTE");
            }
            if (rs.getString("C12FLGREC").equals("P")) {
                cReceber.setFlagSituacao("PAGO");
            }

            cReceber.setVrParcela(rs.getDouble("C12VRPAR"));
            cReceber.setVrPago(rs.getDouble("VALORPGTO"));
            cReceber.setnRecebimento(rs.getString("C12NRECTO"));

            listaContas.add(cReceber);
        }

        stmt.close();
        con.close();

        return listaContas;
    }

    public static List<ContasReceberBean> descJurosRecAnalitico(String dataInicial, String dataFinal, String vendedor) throws SQLException, IOException {

        List<ContasReceberBean> lista = new ArrayList<>();
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT C04CODIGO+'-'+C04NOME AS VENDEDOR, SUM(C12VRPAR-C12VRPGTO) AS DESCONTO\n"
                + "FROM SPAC12 \n"
                + "INNER JOIN SPAC05 ON (C05CODIGO = C12CODCLI) \n"
                + "INNER JOIN SPAC13 ON (C12NRDOC = C13NPED) \n"
                + "INNER JOIN SPAC04 ON (C13VEND = C04CODIGO) \n"
                + "WHERE  C12DTREC BETWEEN '" + dataInicial + "' AND '" + dataFinal + "'\n"
                + "AND C12FLGREC = 'P' \n"
                + "AND (C12VRPAR-C12VRPGTO) > 0\n"
                + "AND C13VEND LIKE '%" + vendedor + "%' \n"
                + "GROUP BY 1\n"
                + "ORDER BY 2 DESC";

        PreparedStatement stm = con.prepareStatement(sql);
        ResultSet rs = stm.executeQuery();

        while (rs.next()) {

            ContasReceberBean cReceber = new ContasReceberBean();

            PedidoBean pedido = new PedidoBean();

            UsuarioBean usuario = new UsuarioBean();
            usuario.setNome(rs.getString("VENDEDOR"));

            pedido.setUsuario(usuario);

            cReceber.setPedido(pedido);
            cReceber.setDescRecebimentos(rs.getDouble("DESCONTO"));

            lista.add(cReceber);
        }

        stm.close();
        con.close();

        return lista;
    }

    public static List<ContasReceberBean> descJurosRecSintetico(String dataInicial, String dataFinal, String vendedor) throws SQLException, IOException {

        List<ContasReceberBean> lista = new ArrayList<>();
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT C04NOME AS VENDEDOR, C12NRDOC, C05NOME, C12DTCOMP, C12DTVEN, C12DTREC, C12VRPAR, C12VRPGTO, C12VRPAR-C12VRPGTO AS C12DESCTO\n"
                + "FROM SPAC12 \n"
                + "INNER JOIN SPAC05 ON (C05CODIGO = C12CODCLI) \n"
                + "INNER JOIN SPAC13 ON (C12NRDOC = C13NPED) \n"
                + "INNER JOIN SPAC04 ON (C13VEND = C04CODIGO) \n"
                + "WHERE  C12DTREC BETWEEN '"+dataInicial+"' AND '"+dataFinal+"' \n"
                + "AND C12FLGREC = 'P' \n"
                + "AND C12VRPAR-C12VRPGTO > 0\n"
                + "AND C13VEND LIKE '%"+vendedor+"%' \n"
                + "ORDER BY 9 DESC";

        PreparedStatement stm = con.prepareStatement(sql);
        ResultSet rs = stm.executeQuery();

        while (rs.next()) {

            ContasReceberBean cReceber = new ContasReceberBean();

            UsuarioBean usuario = new UsuarioBean();
            CaixaBean caixa = new CaixaBean();

            usuario.setNome(rs.getString("VENDEDOR"));
            caixa.setUsuario(usuario);

            cReceber.setCaixa(caixa);

            cReceber.setnDocumento(rs.getString("C12NRDOC"));
            cReceber.setDataCompra(dataCorreta(rs.getString("C12DTCOMP")));
            cReceber.setDataVencimento(dataCorreta(rs.getString("C12DTVEN")));
            cReceber.setDataPagamento(dataCorreta(rs.getString("C12DTREC")));

            cReceber.setVrParcela(rs.getDouble("C12VRPAR"));
            cReceber.setVrPago(rs.getDouble("C12VRPGTO"));
            cReceber.setDescRecebimentos(rs.getDouble("C12DESCTO"));

            ClienteBean cliente = new ClienteBean();
            cliente.setNome(rs.getString("C05NOME"));

            cReceber.setCliente(cliente);

            lista.add(cReceber);
        }

        stm.close();
        con.close();

        return lista;
    }

}
